/*
	EXEC SP_LOAD_PRODUCT_DETAILS '0','','','',''
*/
ALTER PROC SP_LOAD_PRODUCT_DETAILS

@PROCESSSTEP_IDS	VARCHAR(50),
@PACKAGE		VARCHAR(20),
@PRODUCTGROUP		VARCHAR(20),
@PINCOUNT		VARCHAR(20),
@DEFAULT_PROMPT		VARCHAR(50)

AS
BEGIN
	DECLARE @RESULT_TABLE TABLE( [MKT PARTNUMBER] VARCHAR(50),[ASSEMBLY PART NUMBER] VARCHAR(50),[TSMC PART NUMBER] VARCHAR(50),
				[FAB PART] VARCHAR(50),[FAB GDW] VARCHAR(20),PACKAGE VARCHAR(20),
				[PIN COUNT] VARCHAR(20),/*[PRODUCT GROUP] VARCHAR(20),*/[PART STATUS] VARCHAR(50),
				[TEST PLATFORM] VARCHAR(50), [TEST TIME] VARCHAR(50), [TECHNOLOGY] VARCHAR(50), /*[COST ACCOUNT] VARCHAR(20),*/
				[FAB VENDOR] VARCHAR(50), [ASSEMBLY VENDOR1] VARCHAR(50),[ASSEMBLY VENDOR2] VARCHAR(50), 
				[ASSEMBLY VENDOR3] VARCHAR(50),/*[ASSEMBLY VENDOR4] VARCHAR(50),[ASSEMBLY VENDORS] VARCHAR(150),*/[TEST VENDOR1] VARCHAR(50),
				[TEST VENDOR2] VARCHAR(50) /*,[TEST VENDOR3] VARCHAR(50),[TEST VENDOR4] VARCHAR(50)*/ )
	
	DECLARE @PART_TABLE TABLE ( PROCESSSTEP_ID INT, PS_TYPE VARCHAR(50), PRODUCT_NAME VARCHAR(50))
	
	DECLARE @SQLSTRING		NVARCHAR(2000)
	DECLARE @FGPROCESSTEP_ID 	INT
	DECLARE @MKT_PART_NUMBER 	VARCHAR(50)
	DECLARE @FAB_PART_NUMBER 	VARCHAR(50)
	DECLARE @TSMC_PART_NUMBER VARCHAR(50)
	DECLARE @DIEBANK_PART_NUMBER 	VARCHAR(50)
	DECLARE @FAB_PROCESSSTEP_ID 	INT
	DECLARE @ASSEMBLY_PROCESSSTEP_ID INT
	DECLARE @GROSS_DIE		VARCHAR(20)
	DECLARE @PACKAGE_TYPE		VARCHAR(20)
	DECLARE @PIN_COUNT		VARCHAR(20)
	--DECLARE @PRODUCT_GROUP		VARCHAR(20)
	--DECLARE @COST_ACCOUNT		VARCHAR(20)
	DECLARE @PART_STATUS		VARCHAR(50)
	DECLARE @TEST_PROCESSSTEP_ID INT
 
	DECLARE @TEST_PLATFORM VARCHAR(50)
	DECLARE @TEST_TIME VARCHAR(50)
	DECLARE @TECHNOLOGY VARCHAR(50)

	DECLARE @FABVENDOR 	VARCHAR(50)
	DECLARE @ASSEMBLYVENDOR VARCHAR(50)
	DECLARE @TESTVENDOR 	VARCHAR(50)

	DECLARE @ASSEMBLYVENDOR1 VARCHAR(50)
	DECLARE @ASSEMBLYVENDOR2 VARCHAR(50)
	DECLARE @ASSEMBLYVENDOR3 VARCHAR(50)
	--DECLARE @ASSEMBLYVENDOR4 VARCHAR(50)
	--DECLARE @ASSEMBLYVENDORS VARCHAR(50)
	
	DECLARE @TESTVENDOR1 VARCHAR(50)
	DECLARE @TESTVENDOR2 VARCHAR(50)
	--DECLARE @TESTVENDOR3 VARCHAR(50)
	--DECLARE @TESTVENDOR4 VARCHAR(50)
	--DECLARE @TESTVENDORS VARCHAR(50)
	
	DECLARE @FAB_CURSOR CURSOR
	DECLARE @ASSEMBLY_CURSOR CURSOR	
	DECLARE @TEST_CURSOR CURSOR	
	DECLARE @COUNT 		INT

	DECLARE @PRODUCTFLOW_CURSOR CURSOR

	--SET @SQLSTRING = N'
	IF @PROCESSSTEP_IDS IS NOT NULL AND @PROCESSSTEP_IDS <> '0'
	BEGIN 
		SET @PRODUCTFLOW_CURSOR = CURSOR FOR 
		SELECT P.PROCESSSTEP_ID,P_MARKETING_PART_NUMBER,PART_STATUS,COST_CODE FROM PRODUCT P,PROCESS_STEP PS 
		WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS_TYPE = 'FG' AND P_MARKETING_PART_NUMBER IS NOT NULL AND P.PROCESSSTEP_ID = @PROCESSSTEP_IDS
	END
	ELSE
	BEGIN
		SET @PRODUCTFLOW_CURSOR = CURSOR FOR 
		SELECT P.PROCESSSTEP_ID,P_MARKETING_PART_NUMBER,PART_STATUS/*,COST_CODE*/ FROM PRODUCT P,PROCESS_STEP PS 
		WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS_TYPE = 'FG' AND P_MARKETING_PART_NUMBER IS NOT NULL  
	END 
		
	OPEN @PRODUCTFLOW_CURSOR
	FETCH NEXT FROM @PRODUCTFLOW_CURSOR INTO @FGPROCESSTEP_ID,@MKT_PART_NUMBER,@PART_STATUS--,@COST_ACCOUNT
	WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT INTO @PART_TABLE 
		SELECT PROCESSSTEP_ID,PS_TYPE,PRODUCT_NAME FROM FN_GET_PROCESS_FLOW_BY_PROCESSSTEP_ID ( @FGPROCESSTEP_ID ) 

		SELECT @DIEBANK_PART_NUMBER = PRODUCT_NAME FROM @PART_TABLE WHERE PS_TYPE = 'ASSEMBLY'
		SELECT @FAB_PART_NUMBER = PRODUCT_NAME, @FAB_PROCESSSTEP_ID = PROCESSSTEP_ID FROM @PART_TABLE WHERE PS_TYPE = 'FAB'
		SELECT @TSMC_PART_NUMBER = PART_NUMBER FROM PRODUCT_MAP_TABLE WHERE LOCATION_ID = 1 AND PROCESSSTEP_ID IN (SELECT PROCESSSTEP_ID FROM @PART_TABLE)
		SELECT @ASSEMBLY_PROCESSSTEP_ID = PROCESSSTEP_ID FROM @PART_TABLE WHERE PS_TYPE = 'ASSEMBLY'	 	
		SELECT @TEST_PROCESSSTEP_ID = PROCESSSTEP_ID FROM @PART_TABLE WHERE PS_TYPE = 'FT'	 	
		SELECT @GROSS_DIE = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'GROSS DIE/WAFER' AND PROCESSSTEP_ID = @FAB_PROCESSSTEP_ID 		
		SELECT @PACKAGE_TYPE = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'PACKAGE ID' AND PROCESSSTEP_ID = @ASSEMBLY_PROCESSSTEP_ID  	
		SELECT @PIN_COUNT = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'PIN' AND PROCESSSTEP_ID = @FGPROCESSTEP_ID 
		--SELECT @PRODUCT_GROUP = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'PRODUCT GROUP'AND PROCESSSTEP_ID = @FGPROCESSTEP_ID 
		SELECT @TEST_PLATFORM = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'TEST PLATFORM' AND PROCESSSTEP_ID = @TEST_PROCESSSTEP_ID 
		SELECT @TEST_TIME = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'TEST TIME' AND PROCESSSTEP_ID = @TEST_PROCESSSTEP_ID 
		SELECT @TECHNOLOGY = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'TECHNOLOGY' AND PROCESSSTEP_ID = @FAB_PROCESSSTEP_ID 
		SET @COUNT = 1

		SELECT TOP 1  @FABVENDOR = L_VENDOR_NAME FROM PROCESS_STEP_LOCATION_TABLE PSLT, LOCATION L WHERE PSLT.LOCATION_ID = L.LOCATION_ID AND PROCESSSTEP_ID = @FAB_PROCESSSTEP_ID
 					
		SET @ASSEMBLY_CURSOR = CURSOR FOR 
			SELECT DISTINCT L_VENDOR_NAME FROM PROCESS_STEP_LOCATION_TABLE PSLT, LOCATION L WHERE PSLT.LOCATION_ID = L.LOCATION_ID AND PROCESSSTEP_ID = @ASSEMBLY_PROCESSSTEP_ID
		OPEN @ASSEMBLY_CURSOR
		FETCH NEXT FROM @ASSEMBLY_CURSOR INTO @ASSEMBLYVENDOR
		WHILE @@FETCH_STATUS = 0
		BEGIN	
			IF @COUNT = 1
			BEGIN
				SET @ASSEMBLYVENDOR1 = @ASSEMBLYVENDOR
			END
			ELSE IF @COUNT = 2
			BEGIN
				SET @ASSEMBLYVENDOR2 = @ASSEMBLYVENDOR
			END
			ELSE IF @COUNT = 3
			BEGIN
				SET @ASSEMBLYVENDOR3 = @ASSEMBLYVENDOR
			END
			/*ELSE IF @COUNT = 4
			BEGIN
				SET @ASSEMBLYVENDOR4 = @ASSEMBLYVENDOR
			END*/
				SET @COUNT = @COUNT + 1

			FETCH NEXT FROM @ASSEMBLY_CURSOR INTO @ASSEMBLYVENDOR
		END
		
		CLOSE @ASSEMBLY_CURSOR
		DEALLOCATE @ASSEMBLY_CURSOR

		/*SET @ASSEMBLYVENDORS = NULL						
		SET @ASSEMBLY_CURSOR = CURSOR FOR 
			SELECT DISTINCT L_VENDOR_NAME FROM PROCESS_STEP_LOCATION_TABLE PSLT, LOCATION L WHERE PSLT.LOCATION_ID = L.LOCATION_ID AND PROCESSSTEP_ID = @ASSEMBLY_PROCESSSTEP_ID
		OPEN @ASSEMBLY_CURSOR
		FETCH NEXT FROM @ASSEMBLY_CURSOR INTO @ASSEMBLYVENDOR
		WHILE @@FETCH_STATUS = 0
		BEGIN	
			IF ( @ASSEMBLYVENDORS IS NULL )
			BEGIN
				SET @ASSEMBLYVENDORS = @ASSEMBLYVENDOR  
			END
			ELSE
			BEGIN
				SET @ASSEMBLYVENDORS = @ASSEMBLYVENDORS +','+ @ASSEMBLYVENDOR  
			END
			FETCH NEXT FROM @ASSEMBLY_CURSOR INTO @ASSEMBLYVENDOR
		END
		
		CLOSE @ASSEMBLY_CURSOR
		DEALLOCATE @ASSEMBLY_CURSOR	*/

		SET @COUNT = 1
		SET @TEST_CURSOR = CURSOR FOR 
			SELECT DISTINCT L_VENDOR_NAME FROM PROCESS_STEP_LOCATION_TABLE PSLT, LOCATION L WHERE PSLT.LOCATION_ID = L.LOCATION_ID AND PROCESSSTEP_ID = @ASSEMBLY_PROCESSSTEP_ID
		OPEN @TEST_CURSOR
		FETCH NEXT FROM @TEST_CURSOR INTO @TESTVENDOR
		WHILE @@FETCH_STATUS = 0
		BEGIN	
			IF @COUNT = 1
			BEGIN
				SET @TESTVENDOR1 = @TESTVENDOR
			END
			ELSE IF @COUNT = 2
			BEGIN
				SET @TESTVENDOR2 = @TESTVENDOR
			END
			/*ELSE IF @COUNT = 3
			BEGIN
				SET @TESTVENDOR3 = @TESTVENDOR
			END
			ELSE IF @COUNT = 4
			BEGIN
				SET @TESTVENDOR4 = @TESTVENDOR
			END*/
				SET @COUNT = @COUNT + 1

			FETCH NEXT FROM @TEST_CURSOR INTO @TESTVENDOR
		END
		
		CLOSE @TEST_CURSOR
		DEALLOCATE @TEST_CURSOR	
		--SELECT * FROM PROCESS_PARAMETER

		/*PRINT '@TESTVENDORS'+@TESTVENDORS
		PRINT '@ASSEMBLYVENDORS'+@ASSEMBLYVENDORS
		PRINT '@FABVENDORS'+@FABVENDORS*/
		
		INSERT INTO @RESULT_TABLE ( [MKT PARTNUMBER],[ASSEMBLY PART NUMBER],[TSMC PART NUMBER],[FAB PART],[FAB GDW],PACKAGE,
						[PIN COUNT],/*[PRODUCT GROUP],*/[PART STATUS],/*[COST ACCOUNT],*/
						[TEST PLATFORM], [TEST TIME], [TECHNOLOGY],
						[FAB VENDOR], [ASSEMBLY VENDOR1],[ASSEMBLY VENDOR2],[ASSEMBLY VENDOR3],/*[ASSEMBLY VENDOR4],*/ 
						[TEST VENDOR1],[TEST VENDOR2]/*,[TEST VENDOR3],[TEST VENDOR4], [ASSEMBLY VENDORS]*/)
		VALUES ( @MKT_PART_NUMBER,@DIEBANK_PART_NUMBER,@TSMC_PART_NUMBER,@FAB_PART_NUMBER,@GROSS_DIE,@PACKAGE_TYPE,
						@PIN_COUNT,/*@PRODUCT_GROUP,*/@PART_STATUS,/*@COST_ACCOUNT,*/
						@TEST_PLATFORM, @TEST_TIME, @TECHNOLOGY,
						@FABVENDOR,@ASSEMBLYVENDOR1,@ASSEMBLYVENDOR2,@ASSEMBLYVENDOR3,/*,@ASSEMBLYVENDOR4,*/
						@TESTVENDOR1,@TESTVENDOR2 /*,@TESTVENDOR3,@TESTVENDOR4,@ASSEMBLYVENDORS */)

		SET @FGPROCESSTEP_ID  		= NULL
		SET @MKT_PART_NUMBER 	 	= NULL
		SET @FAB_PART_NUMBER 	 	= NULL
		SET @TSMC_PART_NUMBER  	= NULL
		SET @DIEBANK_PART_NUMBER 	= NULL
		SET @FAB_PROCESSSTEP_ID 	= NULL
		SET @ASSEMBLY_PROCESSSTEP_ID 	= NULL
		SET @GROSS_DIE		 	= NULL
		SET @PACKAGE_TYPE		= NULL
		SET @PIN_COUNT		 	= NULL
		--SET @PRODUCT_GROUP		= NULL
		SET @PART_STATUS		= NULL
		--SET @COST_ACCOUNT		= NULL
		SET @ASSEMBLYVENDOR1		= NULL
		SET @TESTVENDOR1		= NULL
		SET @FABVENDOR			= NULL
		SET @ASSEMBLYVENDOR		= NULL
		SET @TESTVENDOR			= NULL
		SET @ASSEMBLYVENDOR2		= NULL
		SET @TESTVENDOR2		= NULL
		SET @ASSEMBLYVENDOR3		= NULL
		--SET @TESTVENDOR3		= NULL
		--SET @ASSEMBLYVENDOR4		= NULL
		--SET @TESTVENDOR4		= NULL
		SET @COUNT			= NULL
		SET @TEST_PROCESSSTEP_ID = NULL
		SET @TEST_PLATFORM = NULL
		SET @TEST_TIME = NULL
		SET @TECHNOLOGY = NULL

		DELETE FROM @PART_TABLE

	FETCH NEXT FROM @PRODUCTFLOW_CURSOR INTO @FGPROCESSTEP_ID,@MKT_PART_NUMBER,@PART_STATUS--,@COST_ACCOUNT
	END
	CLOSE @PRODUCTFLOW_CURSOR
	DEALLOCATE @PRODUCTFLOW_CURSOR	

	SELECT 	[MKT PARTNUMBER],[ASSEMBLY PART NUMBER],[TSMC PART NUMBER],[FAB PART],[FAB GDW],PACKAGE,
		[PIN COUNT],/*[PRODUCT GROUP],*/[PART STATUS],/*[COST ACCOUNT],*/
		[TEST PLATFORM], [TEST TIME], [TECHNOLOGY],
		[FAB VENDOR],[ASSEMBLY VENDOR1],[ASSEMBLY VENDOR2],[ASSEMBLY VENDOR3],--[ASSEMBLY VENDOR4],--[ASSEMBLY VENDORS],
		[TEST VENDOR1],[TEST VENDOR2]--,[TEST VENDOR3],[TEST VENDOR4]
	FROM @RESULT_TABLE ORDER BY  [MKT PARTNUMBER]
END


